package rai.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;

import rai.dto.CompanyDTO;
import rai.dto.DepartmentDTO;
import rai.dto.ExperienceDTO;
import rai.form.AddExpForm;
import rai.form.EditExperienceForm;
import rai.util.Constants;

/**
 * Database operation for EditExpInfo.
 *
 * @author Vu
 *
 */
public class EditExperienceDAO {
    /** Log property. */
    private static Logger log = Logger.getLogger("EditExperience.Start");
    /**
     * Add new Employee Exp Info
     * @param addForm
     * @param empID
     * @return error
     */
    public String addNewExp(AddExpForm addForm, int empID) {
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;
        try {
            query = "select * from departexp "
                    + "where EmpID=" + empID + " and DepartID=" + addForm.getDepartID();
            
            conn = rai.util.Connection.getConnection();
            pStat = conn.prepareStatement(query);
            rs=pStat.executeQuery(query);
            
            if(!rs.next()){
                query = "insert into departexp "
                        + "value('" + addForm.getDepartID() 
                        + "','" + empID + "','" + addForm.getRoleID()
                        + "','" + addForm.getPeriodFrom() + "','" 
                        + addForm.getPeriodTo() + "','" + addForm.getDes() +"',0x00)";
                conn = rai.util.Connection.getConnection();
                pStat = conn.prepareStatement(query);
                pStat.executeUpdate(query);
                
                
            } else {
                return "Data Exists in Database!!!";
            }
        } catch (final Exception sqlEx) {
            log.error("EditExpDAO.addExp" + sqlEx);
        } finally {
            try {
                // rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("EditExpDAO.addExp " + sqlEx);
            }
        }
        log.debug("EditExpDAO.addExp.end");
        return "Information Add Successfully!!!";
    }
    /**
     * Get Company Info
     * @param compID
     * @return
     */
    public CompanyDTO getCompInfo(int compID) {
        CompanyDTO result = new CompanyDTO();      
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;

        try {
            // query command
            query = "select ComName, Address, Phone "
                    + " from company"
                    + " where CompanyID="+compID;
            // get connection
            conn = rai.util.Connection.getConnection();
            pStat = conn.prepareStatement(query);
            // execute query
            rs = pStat.executeQuery();
            // get result
            while (rs.next()) {
                result.setSname(rs.getString("ComName"));
                result.setCompAdd(rs.getString("Address"));
                result.setCompPhone(rs.getString("Phone"));
                
            }

        } catch (final Exception sqlEx) {
            log.error("EditExpInfo.GetCompInfo " + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("EditExpInfo.GetCompInfo " + sqlEx);
            }
        }

        return result;
    }
    /**
     * Get Department according to CompID.
     * @param CompID
     * @param request
     * @param response
     * @return List DepartmentDTO
     */
    public List<DepartmentDTO> getDep(final String compID) {
        DepartmentDTO dto;
        final List<DepartmentDTO> result = new ArrayList<DepartmentDTO>();
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;

        try {
            // query command
            query = "select distinct DepartID,DepartName from department"
                    + " where CompanyID="+compID;
            // get connection
            conn = rai.util.Connection.getConnection();
            pStat = conn.prepareStatement(query);
            // execute query
            rs = pStat.executeQuery();
            // get result
            while (rs.next()) {
                dto = new DepartmentDTO();
                dto.setId(rs.getInt("DepartID"));
                dto.setSname(rs.getString("DepartName"));
                result.add(dto);
            }

        } catch (final Exception sqlEx) {
            log.error("SearchInfoDAO.getDep " + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("SearchInfoDAO.getDep " + sqlEx);
            }
        }

        return result;
    }
    
    /**
     * Update Exp Info.
     *
     * @param editform
     * @param empID
     */
    public void updateexp(final EditExperienceForm editform, final int empID) {
        log.info("EditExperience.UpdateExperience");
        Connection conn = null;
        String query = Constants.BLANK_VALUE;
        // ResultSet rs = null;
        PreparedStatement pStat = null;
        try {
            query = "update departexp de set de.DepartID='"
                    + editform.getDepartID() + "', de.RoleID='"
                    + editform.getRole() + "', de.StartDate='"
                    + editform.getStartDate() + "', de.EndDate='"
                    + editform.getEndDate() + "', de.Description='"
                    + editform.getDescription() + "' where " + "de.DepartID='"
                    + editform.getoldDepartID() + "' and de.EmpID='" + empID
                    + "'";
            conn = rai.util.Connection.getConnection();
            pStat = conn.prepareStatement(query);
            pStat.executeUpdate(query);

        } catch (final Exception sqlEx) {
            log.error("EditExperience.UpdateExp " + sqlEx);
        } finally {
            try {
                // rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("EditExperience.UpdateExp " + sqlEx);
            }
        }
        log.debug("EditExperience.UpdateExp.end");
    }

    /**
     * Get List Department.
     *
     * @param DepartID
     * @return List DepartmentDTO
     */
    public final List<DepartmentDTO> getDepartment(final Integer DepartID) {
        DepartmentDTO dto;
        final List<DepartmentDTO> result = new ArrayList<DepartmentDTO>();
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;

        try {
            // query command
            query = "select DepartID, DepartName "
                    + "from department d "
                    + "where d.CompanyID in "
                    + "(select distinct d.CompanyID from department d where d.DepartID='"
                    + DepartID + "')";
            // get connection
            conn = rai.util.Connection.getConnection();
            pStat = conn.prepareStatement(query);
            // execute query
            rs = pStat.executeQuery();
            // get result

            while (rs.next()) {
                dto = new DepartmentDTO();
                dto.setId(rs.getInt("DepartID"));
                dto.setSname(rs.getString("DepartName"));
                result.add(dto);
            }

        } catch (final Exception sqlEx) {
            log.error("SearchInfoDAO.getDep " + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("SearchInfoDAO.getDep " + sqlEx);
            }
        }

        return result;
    }

    /**
     * Get Exp Info when load form.
     *
     * @param DepartID
     * @param EmpID
     * @return
     */
    public ExperienceDTO getExpInfo(final int DepartID, final int EmpID) {
        Connection conn = null;
        String query = Constants.BLANK_VALUE;
        ResultSet rs = null;
        final ExperienceDTO exp = new ExperienceDTO();
        PreparedStatement pStat = null;
        log.debug("ExperienceDAO.getExperience.start");
        try {
            // query command
            query = "select d.DepartID,EmpID,ComName,Address,Phone,DepartName,StartDate,EndDate,Role,de.Description  "
                    + "from departexp de,department d,company c,role r "
                    + "where de.DepartID = d.DepartID and d.CompanyID=c.CompanyID and de.RoleID=r.RoleID "
                    + "and de.DepartID='"
                    + DepartID
                    + "' and de.EmpID="
                    + EmpID;
            // get connection
            conn = rai.util.Connection.getConnection();
            // prepare statement
            pStat = conn.prepareStatement(query);

            rs = pStat.executeQuery();
            while (rs.next()) {
                exp.setDepartID(rs.getInt("DepartID"));
                exp.setCompName(rs.getString("ComName"));
                exp.setCompAddress(rs.getString("Address"));
                exp.setCompPhone(rs.getString("Phone"));
                exp.setDepartName(rs.getString("DepartName"));
                exp.setStartDate(rs.getString("StartDate"));
                exp.setEndDate(rs.getString("EndDate"));
                exp.setRole(rs.getString("Role"));
                exp.setDescription(rs.getString("Description"));

            }
            log.info(exp.getCompAddress());

        } catch (final Exception sqlEx) {
            log.error("AccessEmpData.getExp" + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("AccessEmpData.getExp" + sqlEx);
            }
        }
        log.debug("EmployeeInfoDAO.getExperience.end");
        return exp;
    }
}
